package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.atsmart.app.model.PositionInfo;
import com.atsmart.app.model.PositionShutDownInfo;

public class ShutDownSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addAttribute(PositionShutDownInfo at) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into position_shutdown (ver, pos,type,reportname,reporttype,imagestr) values(?,?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindString(1, at.ver == null ?"" : at.ver);
			stm.bindString(2, at.pos == null ?"":at.pos);
			stm.bindLong(3, at.type == 0 ? 0: at.type);
			stm.bindString(4, at.reportname == null ? "" : at.reportname);
			stm.bindLong(5, at.reporttype == 0 ? 0 : at.reporttype);
			stm.bindString(6, at.imagestr == null ? "" : at.imagestr);
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}

	public PositionShutDownInfo getAttribute(int type) {
		String sql = " select _id, ver, pos,type,reportname,reporttype,imagestr from position_shutdown where type="
				+ type;
		Cursor cursor = db.rawQuery(sql, null);
		PositionShutDownInfo historie = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				historie = new PositionShutDownInfo();
				historie.id = cursor.getInt(0);
				historie.ver = cursor.getString(1);
				historie.pos = cursor.getString(2);
				historie.type = cursor.getInt(3);
			 
				historie.reportname = cursor.getString(4);
				historie.reporttype = cursor.getInt(5);
				historie.imagestr = cursor.getString(6);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return historie;
	}

	public List<PositionShutDownInfo> getAttributeLit(int type) {
		String sql = " select _id, ver, pos,type,reportname,reporttype,imagestr from position_shutdown where type="
				+ type +" order by _id";
		Cursor cursor = db.rawQuery(sql, null);
		PositionShutDownInfo historie = null;
		List<PositionShutDownInfo> pos = new ArrayList<PositionShutDownInfo>();
		try {
			if (cursor != null && cursor.moveToFirst()) {

				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new PositionShutDownInfo();
					historie.id = cursor.getInt(0);
					historie.ver = cursor.getString(1);
					historie.pos = cursor.getString(2);
					historie.type = cursor.getInt(3);
					
					historie.reportname = cursor.getString(4);
					historie.reporttype = cursor.getInt(5);
					historie.imagestr = cursor.getString(6);
					
					pos.add(historie);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return pos;
	}
	
	public List<PositionShutDownInfo> getAtteibuteLimit(int start_num, int page_items){
		String sql = " select  _id, ver, pos,type,reportname,reporttype,imagestr from position_shutdown limit " + start_num + "," + page_items;
		Cursor cursor = db.rawQuery(sql, null);
		PositionShutDownInfo historie = null;
		List<PositionShutDownInfo> pos = new ArrayList<PositionShutDownInfo>();
		try {
			if (cursor != null && cursor.moveToFirst()) {

				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new PositionShutDownInfo();
					historie.id = cursor.getInt(0);
					historie.ver = cursor.getString(1);
					historie.pos = cursor.getString(2);
					historie.type = cursor.getInt(3);
					 
					historie.reportname = cursor.getString(4);
					historie.reporttype = cursor.getInt(5);
					historie.imagestr = cursor.getString(6);
 					
					pos.add(historie);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return pos;
	}
	
	
	public List<PositionShutDownInfo> getAttributeTopLit(int type) {
		String sql = " select  _id, ver, pos,type,reportname,reporttype,imagestr from position_shutdown where type="
				+ type +" order by _id";
		Cursor cursor = db.rawQuery(sql, null);
		PositionShutDownInfo historie = null;
		List<PositionShutDownInfo> pos = new ArrayList<PositionShutDownInfo>();
		try {
			if (cursor != null && cursor.moveToFirst()) {

				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new PositionShutDownInfo();
					historie.id = cursor.getInt(0);
					historie.ver = cursor.getString(1);
					historie.pos = cursor.getString(2);
					historie.type = cursor.getInt(3);
					 
					historie.reportname = cursor.getString(4);
					historie.reporttype = cursor.getInt(5);
					historie.imagestr = cursor.getString(6);
					
					pos.add(historie);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			cursor.close();
		}
		return pos;
	}
	
	public List<PositionShutDownInfo> getAttributePosLit() {
		String sql = " select  _id, ver, pos,type,reportname,reporttype,imagestr from position_shutdown where type=3 or type=8 "
				+" order by _id";
		Cursor cursor = db.rawQuery(sql, null);
		PositionShutDownInfo historie = null;
		List<PositionShutDownInfo> pos = new ArrayList<PositionShutDownInfo>();
		try {
			if (cursor != null && cursor.moveToFirst()) {

				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new PositionShutDownInfo();
					historie.id = cursor.getInt(0);
					historie.ver = cursor.getString(1);
					historie.pos = cursor.getString(2);
					historie.type = cursor.getInt(3);
					 
					historie.reportname = cursor.getString(4);
					historie.reporttype = cursor.getInt(5);
					historie.imagestr = cursor.getString(6);
					
					pos.add(historie);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			cursor.close();
		}
		return pos;
	}

	
	public void deleteAttribute(int type) {
		db.delete("position_shutdown where type=" + type, null, null);
	}
	
	public void deleteById(int id) {
		db.delete("position_shutdown where _id=" + id, null, null);
	}
}
